package team.lhc.cms.util;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;


/**
 * @author 文
 * @description excel工具类
 */
@Slf4j
public class ExcelUtil {

    /**
     * 导入excel
     * @author 文
     * @param file 文件
     * @param col 属性数
     * @return
     * @throws Exception
     */
    public static List<List<String>> importExcel(MultipartFile file) throws IOException {

        //处理前端传过来的文件格式
        File tempFile = File.createTempFile("temp",null);
        InputStream excel = null;
        file.transferTo(tempFile);
        excel = new FileInputStream(tempFile);


        HSSFWorkbook workbook = new HSSFWorkbook(excel);
        HSSFSheet sheet = workbook.getSheetAt(0);

        List<List<String>> list = new ArrayList<>();
        List<String> temp = null;
        Cell cell;
        //getPhysicalNumberOfRows获取有记录的行数
        int a = sheet.getPhysicalNumberOfRows();
        for(int i = 0;i < sheet.getPhysicalNumberOfRows();i++){
            if (i == 0){
                //跳过第一行(标题行)
                continue;
            }
            Row row = sheet.getRow(i);
            if(null!=row){
                //getLastCellNum获取最后一列
                int b = row.getLastCellNum();
                temp = new ArrayList<>();
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    cell = row.getCell(j);
                    cell.setCellType(CellType.STRING);
                    temp.add(cell.getStringCellValue());
                }
                list.add(temp);


            }
        }
        //关闭workbook
        workbook.close();
        return list;

    }

    /**
     * 导出excel
     * @author 文
     * @param response HttpServletResponse对象
     * @param excelData Excel表格的数据，封装为List<List<String>>
     * @param sheetName sheet的名字
     * @param fileName 导出Excel的文件名
     * @param columnWidth Excel表格的宽度，建议为15
     * @throws IOException 抛IO异常
     */
    public static void exportExcel(HttpServletResponse response,
                                   List<List<String>> excelData,
                                   String sheetName,
                                   String fileName,
                                   int columnWidth) throws IOException {

        //声明一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();

        //生成一个表格，设置表格名称
        HSSFSheet sheet = workbook.createSheet(sheetName);

        //设置表格列宽度
        sheet.setDefaultColumnWidth(columnWidth);

        //写入List<List<String>>中的数据
        int rowIndex = 0;
        for(List<String> data : excelData){
            //创建一个row行，然后自增1
            HSSFRow row = sheet.createRow(rowIndex++);

            //遍历添加本行数据
            for (int i = 0; i < data.size(); i++) {
                //创建一个单元格
                HSSFCell cell = row.createCell(i);

                //创建一个内容对象
                HSSFRichTextString text = new HSSFRichTextString(data.get(i));

                //将内容对象的文字内容写入到单元格中
                cell.setCellValue(text);
            }
        }

        //准备将Excel的输出流通过response输出到页面下载
        //八进制输出流
        response.setContentType("application/octet-stream");

        //设置导出Excel的名称
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);

        //刷新缓冲
        response.flushBuffer();

        //workbook将Excel写入到response的输出流中，供页面下载该Excel文件
        workbook.write(response.getOutputStream());

        //关闭workbook
        workbook.close();
    }
}
